/*
 * DBase.java
 *
 * Created on May 4, 2006, 10:29 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package portfolio.dbHost.dataBases;

import java.awt.event.MouseEvent;
import java.awt.event.MouseListener;
//import java.sql.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import javax.swing.JMenuItem;
import javax.swing.JPopupMenu;
import javax.swing.JTree;
import javax.swing.tree.DefaultMutableTreeNode;
import portfolio.dbHost.dataBases.dbTable.Table;
import portfolio.configMessage.configMessageEvent;
import portfolio.configMessage.configMessageFrame;
import portfolio.configMessage.configMessageListener;
import portfolio.dbHost.Host;
import portfolio.dbHost.dataBases.dbTable.MysqlTable;
import portfolio.dbHost.dataBases.dbTable.OdbcTable;
import portfolio.dbHost.project.Project;
import portfolio.mainScreen;

/**
 *
 * @author ACA
 */
    
public abstract class DBase implements configMessageListener, MouseListener
{
    private mainScreen         mainFrame = null;
    private Host               dbHost    = null;
    private configMessageFrame msgFrame  = null;    
    
    protected Connection       connection     = null;
    protected String           sDBName        = null;
    protected Vector           vTables        = null;
    protected int              iTablesCount   = 0;
    protected Table[]          dbTables;
    
    private JTree              dbaseTree     = null;
    
    final static public String SQL_TRUE  = " TRUE ";
    final static public String SQL_FALSE = " FALSE ";
    
    final private String ACTION_PR0JECT_TAB_DROP_DB    = "Drop DB";
    final private String ACTION_PR0JECT_TAB_CREATE_TBL = "Create Table";
    
    final private String ACTION_NEW_PROJECT_INPUT_DB   = "Input DB";
    final private String ACTION_NEW_PROJECT_PROJECT    = "Project";
    private boolean bInputDB = false;
    private boolean bProject = false;
    /**
     *
     * @param e 
     */
    public void okSelected(configMessageEvent e) 
    {
        try {
            String sCommand = msgFrame.getSQLStatementMnemonic();
            if        (sCommand.equals(this.ACTION_PR0JECT_TAB_DROP_DB)) {
                String sProjectName = null;
                Project project = dbHost.getProject();
                if (project != null)
                    sProjectName = project.getProjectName();
                
                if (!sDBName.equals(sProjectName)) {
                    this.drop();
                    mainFrame.displayMessage("DBase.okSelected 72", this.ACTION_PR0JECT_TAB_DROP_DB + " " + sDBName + " successful");
                } else {
                    mainFrame.displayMessage("DBase.okSelected 74", this.ACTION_PR0JECT_TAB_DROP_DB + " " + sDBName + " unsuccessful; project in use.");                    
                }
            } else if (sCommand.equals(this.ACTION_PR0JECT_TAB_CREATE_TBL)) {
                Vector vSqlCreateParams = msgFrame.getSqlCreateParams ();
                int iSqlCreateSize = vSqlCreateParams.size();
                if (iSqlCreateSize == 2) {
                    String sTable   = vSqlCreateParams.get (0).toString();
                    String sColumns = vSqlCreateParams.get (1).toString();
                    Table.create (connection, sDBName, sTable, sColumns);
                    mainFrame.displayMessage("DBase.okSelected 83", this.ACTION_PR0JECT_TAB_CREATE_TBL + " " + sTable + " successful");                    
                } else {
                    new Exception ("Wrong number of paramenters.");
                }
            }
            dbHost.buildDBTree();
            dbHost.updateDBTree(); //mainFrame.getDBasePanel());
            
            msgFrame.dispose();
        } catch (Exception ex) {
            mainFrame.displayMessage("DBase.okSelected 95", ex.getMessage());
        }
    }
    /**
     * Mouse listener abstract members:
     *  mouseClicked, mousePressed, mouseReleased, mouseEntred.
     */
    public void mouseClicked(MouseEvent e) {
//        throw new UnsupportedOperationException("Not supported yet.");
    }

    public void mousePressed(MouseEvent e) {
System.out.println ("------------------------------- DBase: MOUSE PRESSED ------------------------");        
 //       throw new UnsupportedOperationException("Not supported yet.");
        Object o = e.getSource();
        JMenuItem jTempMenuItem = (JMenuItem) o;

        try {
            String sCommand = jTempMenuItem.getActionCommand ();
            if (sCommand.equals (this.ACTION_PR0JECT_TAB_DROP_DB)) {
                msgFrame = new configMessageFrame (mainFrame, this.sDBName, this.ACTION_PR0JECT_TAB_DROP_DB, 250, 120);
                msgFrame.showMessageFrame();
                msgFrame.addConfigMessageListener(this);
            } else if (sCommand.equals(this.ACTION_PR0JECT_TAB_CREATE_TBL)) {
                Vector vCreateParams = new Vector ();
                vCreateParams.add(this.ACTION_PR0JECT_TAB_CREATE_TBL);
                vCreateParams.add ("Table: ");
                vCreateParams.add ("Column definition:");
                msgFrame = new configMessageFrame (mainFrame, sDBName, vCreateParams, 250+ 50, 120 +30);
                msgFrame.showMessageFrame();
                msgFrame.addConfigMessageListener(this);
            } else if (sCommand.equals(this.ACTION_NEW_PROJECT_INPUT_DB)) {
                this.bInputDB = true;
            } else if (sCommand.equals(this.ACTION_NEW_PROJECT_PROJECT)) {
                this.bProject = true;
            }
        } catch (Exception ex) {
            mainFrame.displayMessage("DBase.mousePressed 135", ex.getMessage());
        }
   }

    public void mouseReleased(MouseEvent e) {
 //       throw new UnsupportedOperationException("Not supported yet.");
    }

    public void mouseEntered(MouseEvent e) {
 //       throw new UnsupportedOperationException("Not supported yet.");
    }

    public void mouseExited(MouseEvent e) {
//        throw new UnsupportedOperationException("Not supported yet.");
//        e.equals(MouseEvent.BUTTON1);
    }
    /**
     *
     * @param e 
     */
    public void cancelSelected(configMessageEvent e) 
    {
        msgFrame.dispose();
    }
    /**
     * Creates a new instance of DBase
     * @param mainFrameParam 
     * @throws java.lang.Exception 
     * @param connectionParam 
     * @param sDBNameParam 
     */
    public DBase (mainScreen mainFrameParam,
                     Connection connectionParam,
                     String sDBNameParam) throws Exception 
    {
        mainFrame = mainFrameParam;
        if (mainFrame == null)
            throw new Exception ("null mainFrame parameter.");
        
        if (connectionParam == null)
            throw new Exception ("null connection.");
            
        if (sDBNameParam == null)
            throw new Exception ("null DBName parameter.");
        sDBName    = sDBNameParam;        
        
        connection  = connectionParam;
        if (connection == null)
            throw new Exception ("null connection.");
        
        dbTables   = null;
        try {
            this.setTablesVector ();
        } catch (SQLException e) {
            throw new Exception (e);
        }
    } /* end constructor DBase */
    /**
     * 
     * @param dbHostParam 
     * @throws java.lang.Exception 
     * @param sDBNameParam 
     */
    public DBase (Host dbHostParam, String sDBNameParam) throws Exception 
    {
        dbHost = dbHostParam;
        if (dbHost == null)
            throw new Exception ("null dbHost parameter.");
        mainFrame = dbHost.getMainScreen();
        if (mainFrame == null)
            throw new Exception ("null mainFrame parameter.");
            
        if (sDBNameParam == null)
            throw new Exception ("null DBName parameter.");
        sDBName    = sDBNameParam;        
        
        connection  = dbHost.getConnection();
        if (connection == null)
            throw new Exception ("null connection.");
        
        dbTables   = null;
        try {
            this.setTablesVector ();
        } catch (SQLException e) {
            throw new Exception (e);
        }
    } /* end constructor DBase */
    /*
     * 
     * 
     */
    public DBase (Connection connectionParam, String sDBNameParam) throws Exception 
    {        
        connection  = connectionParam;
        
        if (connection == null)
            throw new Exception ("null connection.");
        
        dbTables   = null;
        try {
            this.setTablesVector ();
        } catch (SQLException e) {
            throw new Exception (e);
        }
    } /* end constructor DBase */
    /**
     * 
     */
    public DBase (Host dbHostParam) throws Exception 
    {
        dbHost = dbHostParam;
        if (dbHost == null)
            throw new Exception ("null dbHost parameter.");
        mainFrame = dbHost.getMainScreen();
        if (mainFrame == null)
            throw new Exception ("null mainFrame parameter.");
            
        sDBName    = null;        
        
        connection  = dbHost.getConnection();
        if (connection == null)
            throw new Exception ("null connection.");
        
        dbTables   = null;
    } /* end constructor DBase */
    /**
     * 
     */
    public void setDBName (String sDBNameParam) throws Exception 
    {
        if (sDBNameParam ==null)
            throw new Exception ("No DBName provided.");
        
        sDBName = sDBNameParam;
    }
    /**
     * 
     */
    public abstract void setTablesVector () throws Exception;
//    public void setTablesVector () throws Exception
//    {
//        DBase.useDB(connection, sDBName);
//        vTables = DBase.showTables(connection, sDBName);
//        if (vTables == null)
//            return;
//        iTablesCount = vTables.size();
//        if (iTablesCount > 0) {
//            dbTables = new Table [iTablesCount];
//            for (int iIdx = 0; iIdx < iTablesCount; iIdx++) {
//                if (this.getDBHost().getDBType() == Host.DB_TYPE_MYSQL) {
//                    dbTables [iIdx] = new MysqlTable (this, vTables.get(iIdx).toString());
//                } else if (this.getDBHost().getDBType() == Host.DB_TYPE_ODBC) {
//                    dbTables [iIdx] = new OdbcTable (this, vTables.get(iIdx).toString());
//
//                }
//            }
//        }
//    }
    /**
     *
     * @return 
     */
    public String getDBName ()
    {
        return sDBName;
    }
    /**
     *
     * @return 
     */
    public int getTableCounter ()
    {
        return iTablesCount;
    }
    /**
     *
     * @return 
     */
    public abstract Vector showTables ();
//    {
//        return vTables;
//    }
    /**
     * 
     * @return 
     */
    public Table [] getTables ()
    {
        return dbTables;
    }
    /**
     *
     * @param iIdx 
     * @return 
     * @throws java.lang.Exception 
     */
    public Table getTable (int iIdx) throws Exception
    {
        if (iIdx < 0 || iIdx > iTablesCount)
            throw new Exception ("improper index.");
        
        return dbTables [iIdx];
    }
    /**
     *
     * @param sTableName 
     * @return 
     */
    public Table isTable (String sTableName)
    {
        if (!vTables.contains (sTableName))
            return null;
        
        int iTableIndex = vTables.indexOf(sTableName);
        
        return dbTables [iTableIndex];        
    }
    /**
     *
     * @param sTableName 
     * @return 
     */
    public boolean isItTable (String sTableName)
    {
        if (vTables == null)
            return false;
        
        if (!vTables.contains(sTableName))
            return false;
        
        return true;
    }
    /**
     *
     * @return 
     */
    public DefaultMutableTreeNode processTreeHierarchy ( ) 
    {
        DefaultMutableTreeNode node = new DefaultMutableTreeNode (sDBName);
        DefaultMutableTreeNode child = null;

        if (dbTables == null)
            return node;
        
        if (dbTables.length <= 0)
            return node;
        
        for (int i = 0; i < dbTables.length; i++) {
            child = dbTables [i].processTreeHierarchy();
            node.add (child);
        }
        return (node);
    } /* end method processHierarchy */ 
    /**
     *
     * @param dbTreeParam 
     * @param iXPos 
     * @param iYPos 
     */
    public void getPopupOptions (JTree dbTreeParam, int iXPos, int iYPos)
    {
        dbaseTree = dbTreeParam;       

        JPopupMenu treeOptionsMenu = new JPopupMenu ("Options");
        dbaseTree.add(treeOptionsMenu);

        JMenuItem jItem1 = new JMenuItem (this.ACTION_PR0JECT_TAB_DROP_DB);
        jItem1.addMouseListener(this);
        
        JMenuItem jItem2 = new JMenuItem (this.ACTION_PR0JECT_TAB_CREATE_TBL);
        jItem2.addMouseListener(this);
        
        treeOptionsMenu.add (jItem1);
        treeOptionsMenu.add (jItem2);
        
        treeOptionsMenu.setLocation (iXPos, iYPos);
        treeOptionsMenu.show (treeOptionsMenu, iXPos, iYPos);

        return;
    } /* end method getPopupOptions */     
    /**
     *
     * @param oPathParam 
     * @return 
     */
    public Object getTreeObject (Object [] oPathParam)
    {
        if (oPathParam == null) {
            //error, no path param
            return null;
        }
        if (oPathParam.length < 2) {
            return null;
        }
        if (!oPathParam [1].toString().equals(sDBName)) {
            //error, no database found
            return null;
        }
        if (oPathParam.length != 2) {
            String sTable = oPathParam [2].toString();
            int iIdx = vTables.indexOf(sTable);
            if (iIdx != -1) {
                return dbTables[iIdx].getTreeObject (oPathParam);
            } else {
                //error, no database found
                return null;
            }
        }
        return this;
    } /* end method getTreeObject */
    /**
     *
     * @return 
     */
    public Host getDBHost ()
    {
        return dbHost;
    }
     /**
      *
      * @param dbTreeParam 
      * @param iXPos 
      * @param iYPos 
      */
    public void getProjectPopupOptions (JTree dbTreeParam, int iXPos, int iYPos)
    {
        if (dbTreeParam == null) {
            mainFrame.displayMessage("DBase.getProjectPopupOptions 443", "null tree.");
            return;
        }

        JPopupMenu treeOptionsMenu = new JPopupMenu ("Options");
        dbTreeParam.add(treeOptionsMenu);

        JMenuItem jInputDBItem = new JMenuItem (this.ACTION_NEW_PROJECT_INPUT_DB);
        jInputDBItem.addMouseListener (this);
        treeOptionsMenu.add (jInputDBItem); 

        JMenuItem jProjectItem = new JMenuItem (this.ACTION_NEW_PROJECT_PROJECT);
        jProjectItem.addMouseListener (this);
        treeOptionsMenu.add (jProjectItem);
        
        treeOptionsMenu.setLocation (iXPos, iYPos);
        treeOptionsMenu.show (treeOptionsMenu, iXPos, iYPos);
            
        return;
    } /* end method getPopupOptions */
    /**
     *
     * @return 
     */
    public boolean getInputDBFlag ()
    {
        return bInputDB;
    }
    public boolean getProjectFlag ()
    {
        return bProject;
    }
    /**
     *
     * @param bFlagParam 
     */
    public void setInputDBFlag (boolean bFlagParam)
    {
        bInputDB          = bFlagParam;
    }        
    public void setProjectFlag (boolean bFlagParam)
    {
        bProject          = bFlagParam;
    }
    /**
     *
     * @param sTableNameParam 
     * @return 
     */
    public Table getTable (String sTableNameParam)
    {
        if (vTables == null)
            return null;
        
        if (!vTables.contains(sTableNameParam)) {
            return null;
        }
        int iDBaseIdx = vTables.indexOf(sTableNameParam);
        
        return dbTables [iDBaseIdx];        
    }
    /**
     * 
     * @return 
     */
    public JTree getDBTree ()
    {
        return dbaseTree;
    }
    /******************************************************************
     * Protected against SQL injection
     ******************************************************************/
    static public void drop (Connection connectionParam, String sDataBase) throws Exception
    {
        if (connectionParam == null)
            throw new Exception ("No connection provided.");
        if (sDataBase == null)
            throw new Exception ("No data base name provided.");
        
        try {
            PreparedStatement pstmt = connectionParam.prepareStatement("DROP DATABASE " + sDataBase);
            pstmt.execute ();            
        }
        catch (SQLException sqlex) {
            throw new Exception (sqlex);
        }
        return;
    } 
    /**
     * 
     */
    public void drop () throws Exception
    {
        DBase.drop (connection, sDBName);
    }
    /**
     *
     *
     * @param connectionParam
     * @param sDBNameParam
     * @return
     */
    public static boolean useDB (Connection connectionParam, String sDBNameParam)
    {
        try {
//0j0            String sCatalog = connectionParam.getCatalog();
//0j0            sCatalog = sCatalog.toLowerCase();
//0j0            System.out.println ("Connection catalog: " + sCatalog + "<<<<<<<<<<<<<<<<");
            sDBNameParam = sDBNameParam.toLowerCase();
            String sDriverName = connectionParam.getMetaData ().getDriverName();
            sDriverName = sDriverName.toLowerCase();
            System.out.println ("SQL driver name: " + sDriverName + "<<<<<<<<<<<<<<<<<<<<<<<<");
            System.out.println ("Native SQL statement: " + connectionParam.nativeSQL("USE " + sDBNameParam + ";") + "<<<<<<<<<<<<<<<<<<<<<");
            String sDriverVersion = connectionParam.getMetaData().getDriverVersion();
            System.out.println ("Driver Version: " + sDriverVersion);
            String sSchemaTerm = connectionParam.getMetaData().getSchemaTerm();
            System.out.println ("Schema term: " + sSchemaTerm);
//0j0            if (sDriverName.contains("odbc") == true) {
//0j0                if (sCatalog.contains(sDBNameParam) == true){
//0j0                    return true;
//0j0                }
//0j0                return false;
//0j0            }

            Statement statement = connectionParam.createStatement();
            statement.execute ("USE " + sDBNameParam + ";");
        }
        catch ( Exception sqlex ) {
            System.out.println(sqlex.getMessage());
            return false;
        }
        return true;
    }
    /**
     * 
     * @param hostParam
     * @param sDBNameParam
     * @return
     */
    public static boolean useDB (Host hostParam, String sDBNameParam)
    {
        Connection conTemp = hostParam.getConnection();
        return DBase.useDB (conTemp, sDBNameParam);
    }
    /**
     *
     * @param connectionParam
     * @param sDBNameParam
     * @return
     * @throws Exception
     */
//    public static Vector showTables (Connection connectionParam,
//                                       String sDBNameParam) throws Exception
//    {
//        if (connectionParam == null)
//            return null;
//
//        Vector outVector = new Vector ();
//
//        Statement statement = connectionParam.createStatement ();
//        System.out.println ("showTables catalog: " + connectionParam.getCatalog());
////        statement.execute ("USE "+ sDBNameParam + ";");
////Original        ResultSet resultSet = statement.executeQuery ("SHOW TABLES;");
////        ResultSet resultSet = statement.executeQuery ("SELECT table_name FROM INFORMATION_SCHEMA.TABLES" +
////                              " WHERE table_schema = '" + sDBNameParam + "';");
//        String sNativeStmt = connectionParam.nativeSQL("SELECT table_name FROM " + "INFORMATION_SCHEMA.TABLES;");
//        System.out.println("Native statement: " + sNativeStmt);
//        ResultSet resultSet = statement.executeQuery (sNativeStmt); //"SELECT table_name FROM " + "syscalctable;");
//
//        if (!resultSet.next ()) {
//            return null;
//        }
//
//        do {
//            String sTmp = resultSet.getString (1).toLowerCase();
////System.out.println (sTmp+"<<<<<<<<");
//            if (!outVector.contains(sTmp)) {
//                outVector.addElement (sTmp);
//            }
//        } while (resultSet.next ());
//
//        return (outVector);
//    } /* End Method PortfolioMainScr::stocksMenu::showDBTables */
} /* end class DBase */
